import nodeXlsx, { WorkSheet } from 'node-xlsx';
import * as path from 'path';
import fse = require('fs-extra');
import mysql = require('mysql2');
import { DictionaryWords } from '@/entity/dictionary-words.entity';
// console.log(mysql);

// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'partner_seeking',
});

// simple query
// connection.query(
//   'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
//   function (err, results, fields) {
//     console.log(results); // results contains rows returned by server
//     console.log(fields); // fields contains extra meta data about results, if available
//   },
// );
const query = (
  selectStr: string,
): Promise<
  [
    (
      | mysql.RowDataPacket[]
      | mysql.RowDataPacket[][]
      | mysql.OkPacket
      | mysql.OkPacket[]
      | mysql.ResultSetHeader
    ),
    mysql.FieldPacket[],
  ]
> => {
  return new Promise((resolve, reject) => {
    connection.query(selectStr, function (err, results, fields) {
      if (err) {
        reject(err);
      }
      // console.log(results); // results contains rows returned by server
      // console.log(fields); // fields contains extra meta data about results, if available
      resolve([results, fields]);
    });
  });
};

// with placeholder
// connection.query(
//   'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
//   ['Page', 45],
//   function (err, results) {
//     console.log(results);
//   },
// );

(async () => {
  const [newSheets] = await query('SELECT * FROM `wine_cet4_word`');
  type List = {
    id: number | string;
    cet4_word: string;
    cet4_phonetic: string;
    cet4_translate: string;
    cet4_distortion: string;
    cet4_phrase: string;
    cet4_samples: string;
  };
  const list = newSheets as List[];
  const newList = [
    ['单词', '单词词义', '单词发音'],
    ...list.map((item) => {
      const _item = {
        wordContent: item.cet4_word,
        wordPronunciation: item.cet4_phonetic,
        wordTranslate: item.cet4_translate,
      };
      return [_item.wordContent, _item.wordTranslate, _item.wordPronunciation];
    }),
  ];
  const sheetList: WorkSheet<string | number>[] = [
    {
      name: '四级单词词汇',
      data: newList,
      options: {},
    },
  ];
  const dataBuffer = nodeXlsx.build(sheetList);

  const storePath = path.resolve(__dirname, './excel');

  fse.ensureDir(storePath);

  fse.writeFileSync(
    path.resolve(storePath, './四级词汇.xlsx'),
    Buffer.from(dataBuffer),
  );
})();

// const dataBuffer = nodeXlsx.build(newSheets);

// const storePath = path.resolve(__dirname, './excel');

// fse.ensureDir(storePath);

// fse.writeFileSync(
//   path.resolve(storePath, './全部数据4_16_3_35修改版now.xlsx'),
//   Buffer.from(dataBuffer),
// );
